package com.atguigu.eduservice.utils;

import com.alibaba.fastjson.JSONObject;
import com.atguigu.servicebase.polo.R;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.*;

public class ExcelCommon {
    private XSSFSheet ExcelWSheet;
    private XSSFWorkbook ExcelWBook;
    private XSSFCell Cell;
    private XSSFRow row;
    private String filePath;

    //设定要操作的Excel 的文件路径和Excel 文件中的sheet名称
    //在读写excel的时候，均需要先调用此方法，设定要操作的excel 文件路径和要操作的sheet名称
    //构造函数初始化
    public
    ExcelCommon(String Path, String SheetName) throws Exception {
        FileInputStream ExcelFile;

        try {
            //实例化excel 文件的FileInputStream 对象
            ExcelFile = new FileInputStream(Path);
            //实例化excel 文件的XSSFWorkbook 对象
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            //实例化ExcelWSheet 对象，指定excel 文件中的sheet 名称，后续用于sheet 中行、列和单元格的操作
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
        } catch (Exception e) {
            throw (e);
        }
        filePath = Path;
    }

    //读取指定单元格的数据。需要传入行数、列数
    public
    String getCellData(int RowNum, int ColNum) throws Exception {
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            String CellData = "";
            if (Cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                CellData = Cell.getStringCellValue();
            } else if (Cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                DecimalFormat df = new DecimalFormat("0");
                CellData = df.format(Cell.getNumericCellValue());
            }
            return CellData;
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }
    }

    //读取指定单元格的数据且值为字符串。需要传入行数、列数
    public
    String getCellStrData_value(int RowNum, int ColNum) throws Exception {
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            if (Cell != null) {
                String CellData = Cell.getStringCellValue();
                return CellData;
            } else {
                return "";
            }
        } catch (Exception e) {
            return "";
        }
    }

    //读取指定单元格的数据且值为数字。需要传入行数、列数
    public
    String getCellNumData_value(int RowNum, int ColNum) throws Exception {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            if (Cell != null) {
                if (Cell.getCellType() == 1) {
                    cellValue = Cell.getStringCellValue();
                } else {
                    cellValue = String.valueOf(Cell.getNumericCellValue());
                    if (cellValue.endsWith(".0")) {
                        cellValue = cellValue.substring(0, cellValue.length() - 2);
                    }
                }
            }
            return cellValue;
        } catch (Exception e) {
            return "";
        }
    }

    // 在excel 文件的执行单元格中写入数据，此函数只支持后缀为xlsx的excel 文件写入
    public
    void setCellData(int RowNum, int ColNum, String Result) throws Exception {
        try {
            // 获取excel文件中的行对象
            row = ExcelWSheet.getRow(RowNum);
            // 如果单元格为空，则返回Null
            Cell = row.getCell(ColNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);

            if (Cell == null) {
                // 当单元格对象是null 的时候，则创建单元格
                // 如果单元格为空，无法直接调用单元格对象的setCellValue 方法设定单元格的值
                Cell = row.createCell(ColNum);
                // 创建单元格后可以调用单元格对象的setCellValue 方法设定单元格的值
                Cell.setCellValue(Result);
            } else {
                // 单元格中有内容，则可以直接调用单元格对象的s方法设定单元格的值etCellValue
                Cell.setCellValue(Result);
                System.out.println("执行完成");
            }
            // 实例化写入 excel 文件的文件输出流对象
            FileOutputStream fileOut = new FileOutputStream(filePath);
            // 将内容写入excel 文件中
            ExcelWBook.write(fileOut);
            // 调用flush方法强制刷新写入文件
            fileOut.flush();
            // 关闭文件输出流对象
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw (e);
        }
    }

    // 从excel 文件获取测试数据的静态方法
    public static
//    Object[][] getTestData(String execlFilePath, String sheetName) throws IOException {
     //List<JSONObject>  getTestData(HashMap<String,Object>  fieldlist,String execlFilePath, String sheetName) throws IOException {
        // 根据参数传入的数据文件路径和文件名称，组合出excel 数据文件的绝对路径
        // 声明一个file 文件对象
    List<JSONObject>  getTestData(HashMap<String,Object>  fieldlist, MultipartFile file, String sheetName) throws  Exception {
        List<JSONObject> dataList = new ArrayList<>();
        if (StringUtils.isEmpty(fieldlist) || fieldlist.size() == 0) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("error", "导入参数列为空");
            dataList.add(jsonObject);
            return dataList;

        }
        //File file = new File(execlFilePath);

        // 创建FileInputStream 对象用于读取excel 文件
        // FileInputStream inputStream = new FileInputStream(file);
        InputStream inputStream = file.getInputStream();
        //FileInputStream inputStream = file.getInputStream();


        // 声明Workbook 对象
        Workbook Workbook = null;

        // 获取文件名参数的后缀名，判断xlsx文件还是xls文件
        //String fileExtensionName = execlFilePath.substring(execlFilePath.indexOf("."));
        String fileExtensionName = file.getOriginalFilename();
        fileExtensionName = fileExtensionName.substring(fileExtensionName.indexOf("."));

        // 判断文件类型如果是xlsx，则使用XSSFWorkbook 对象进行实例化
        // 判断文件类型如果是xls，则使用HSSFWorkbook 对象进行实例化
        Boolean isxls = false;
        if (fileExtensionName.equals(".xlsx")) {
            //如果是2007的，也就是.xlsx， 让Workbook = new XSSFWorkbook(inputStream);
            Workbook = new XSSFWorkbook(inputStream);

        } else if (fileExtensionName.equals(".xls")) {
            //如果是2003的，也就是.xls， 让Workbook = new HSSFWorkbook(inputStream);
            Workbook = new HSSFWorkbook(inputStream);
            isxls = true;
        }

        // 通过sheetName参数，生成sheet 对象
        Sheet Sheet = Workbook.getSheet(sheetName);

        // 获取excel 数据文件中sheet1中数据的行数，getLastRowNum 方法获取数据的最后行号
        // getFirstRowNum 方法获取数据的第一行行号，相减之后算出数据的行数
        // 注意：excel 文件的行号和列号都是从0开始
        int rowCount = Sheet.getLastRowNum() - Sheet.getFirstRowNum();
        // 创建名为records 的list 对象来存储从excel数据文件读取的数据
        List<Object[]> records = new ArrayList<Object[]>();
        // 使用2个for 循环遍历excel 数据文件的所有数据（除了第一行，第一行是数据列名称）
        // 所以i 从1开始，而不是从0
        Row row = null;
        row = Sheet.getRow(0);
        String title[] = new String[row.getLastCellNum()];


        for (int j = 0; j <= row.getLastCellNum() - 1; j++) {

            try {
                if (row.getCell(j).getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    title[j] = row.getCell(j).getStringCellValue().trim();
                } else if (row.getCell(j).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    DecimalFormat df = new DecimalFormat("0");
                    title[j] = df.format(row.getCell(j).getNumericCellValue());
                } else {
                    System.out.println("格式错误");
                }

            } catch (Exception e) {
                title[j] = "";
            }

        }
        List tempList = Arrays.asList(title);
        //System.out.println(tempList);
        String error = "";
        //for (int i=0;i<=fieldlist.size()-1;i++);
        for (Map.Entry<String, Object> entry : fieldlist.entrySet()) {


            if (tempList.indexOf(entry.getKey()) == -1) {
                error = "字段" + entry.getKey() + "未包含在标题内," + error;
            }
        }
        if (error.length() != 0) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("error", error);
            dataList.add(jsonObject);
            return dataList;
        }


        for (int i = 1; i < rowCount + 1; i++) {
            // 使用getRow 方法获取行对象
            row = Sheet.getRow(i);
            JSONObject jsonObject = new JSONObject();

            String fields[] = new String[row.getLastCellNum()];

            // if (row.getCell(row.getLastCellNum() - 1).getStringCellValue().equals("y")) {
            for (int j = 0; j <= row.getLastCellNum() - 1; j++) {
                //判断excel 的单元格字段是数字还是字符，字符串格式调用getStringCellValue 方法获取
                // 数字格式调用getNumericCellValue 方法获取
                // fields[j-1]=(String) row.getCell(j).getCellType()==;
                if (Strings.isBlank(title[j])) continue;
                if (title[j].equals("产品图片")) continue;
                try {
                    if (row.getCell(j).getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        fields[j] = row.getCell(j).getStringCellValue();
                    } else if (row.getCell(j).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                        DecimalFormat df = new DecimalFormat("0.00");
                        fields[j] = df.format(row.getCell(j).getNumericCellValue());
                    } else {
                        System.out.println("格式错误");
                    }
                } catch (Exception e) {
                    fields[j] = "";
                }
                if (fieldlist.containsKey(title[j]))
                    jsonObject.put(fieldlist.get(title[j]).toString(), fields[j]);

                //}
                // fields 的数据对象存储到records的list中

            }
            records.add(fields);
            dataList.add(jsonObject);
        }
        Map<String, PictureData> pictureDataMap = null;

        if (fieldlist.containsKey("产品图片")) {
            //sheet = Workbook.getSheetAt(0);

            if (isxls)
                pictureDataMap = getXlsPictures((HSSFSheet) Sheet,dataList);
        else {
            pictureDataMap = getXlsxPictures((XSSFSheet) Sheet,dataList);
        }
            System.out.println(pictureDataMap);

    }


        // 定义函数返回值，即Object[][]
        // 将存储测试数据的list 转换为一个Object 的二维数组
        //System.out.println(dataList);
        Object[][] results = new Object[records.size()][];
        // 设置二维数组每行的值，每行是个object对象

        for (int i = 0; i < records.size(); i++) {

            results[i] = records.get(i);
            //LogUtil.logInfo(results[i]);

        }
        // LogUtil.logInfo(results);
        // 关闭excel 文件
        //System.out.println(results);
        //return R.ok().data("list",dataList);
        return dataList;
    }

    public
    int getLastCellNum() {
        // 返回数据文件的最后一列的列号，如果有12列，则结果返回11
        return ExcelWSheet.getRow(0).getLastCellNum() - 1;
    }

    //public HashMap getProxyPojoValue(Object object, Set<String> key1){
    public static HashMap getProxyPojoValue(Object object, Set<String> key1){
        String id = null;
        // 返回参数
        HashMap<String,Object> hashMap = new HashMap<>(16);
        //for (String s : key1) {
            Field[] fields = object.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);

                // 获取表名
//                TableName table = object.getClass().getAnnotation(TableName.class);
//                if (table != null) {
//                    String tableName = table.value();
//                    hashMap.putIfAbsent("tableName", tableName);
//                }
                // 获取主键id
                if (id == null) {
                    boolean isIdField = field.isAnnotationPresent(TableId.class);
                    if (isIdField) {
                        TableField tableField = field.getAnnotation(TableField.class);
                        //if (s.toLowerCase().equals(field.getName().toLowerCase())) {
                            //System.out.println(field.getName());
                            String tableId = tableField.value();
                        tableId = tableId.replaceAll("(?:\\[|null|\\]| +)", "");

                        hashMap.put(tableId,field.getName());
                            id = tableId;
                        }
                    //}
                }

                // 获取字段的值
                boolean isTableField = field.isAnnotationPresent(TableField.class);
                if (isTableField) {
                    TableField tableField = field.getAnnotation(TableField.class);
                    //if (s.toLowerCase().equals(field.getName().toLowerCase())) {
                        String fieldValue = tableField.value();
                    fieldValue = fieldValue.replaceAll("(?:\\[|null|\\]| +)", "");

                    hashMap.put(fieldValue,field.getName());
                    //}
                }
            }
        //}
       return(hashMap);
    }
    public static Map<String, PictureData> getXlsPictures(HSSFSheet sheet,List<JSONObject>dataList) throws  Exception {
        Map<String, PictureData> map = new HashMap<>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
//                JSONObject jsonObject=dataList.get(cAnchor.getRow1()-1);

                String itemno=dataList.get(cAnchor.getRow1()-1).getString("ItemNo");
                PictureData pic =picture.getPictureData();
                String ext = pic.suggestFileExtension();

                byte[] data = pic.getData();

                //图片保存路径
                String picStorePath = "D:\\xml\\";
                String imgPath = picStorePath + itemno + "." + ext;
                FileOutputStream out = new FileOutputStream(imgPath);
                out.write(data);
                out.close();


                dataList.get(cAnchor.getRow1()-1).put("Photo",data);
                map.put(itemno, picture.getPictureData());
            }
        }
        return map;
    }
    public static Map<String, PictureData> getXlsxPictures(XSSFSheet sheet,List<JSONObject>dataList) throws  Exception {
        Map<String, PictureData> map = new HashMap<>(4);
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    // 行号-列号
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                    PictureData pic =picture.getPictureData();


                    String itemno=dataList.get(marker.getRow()-1).getString("ItemNo");

                    String ext = pic.suggestFileExtension();

                    byte[] data = pic.getData();

                    //图片保存路径
                    String picStorePath = "D:\\xml\\";
                    String imgPath = picStorePath + itemno + "." + ext;
                    FileOutputStream out = new FileOutputStream(imgPath);
                    out.write(data);
                    out.close();


                    dataList.get(marker.getRow()-1).put("Photo",data);
                }
            }
        }
        return map;
    }



}